from flask import Flask,request,render_template
import json,requests
from MySQLHelper import MySQLHelper
from lxml import etree
from SpeakModel import *
from txtmodel import *

power=0
uid=0
bbname=""

app = Flask(__name__,static_folder="static",template_folder="templates1")

#用户列表
@app.route("/getUserInfo",methods=["get"])
def getUserInfo():
    global power
    if(power==1):
        conn=MySQLHelper();
        sql="select * from tb_user"
        rs=conn.executeForSelectAll(sql,())
        return json.dumps(rs)
    else:
        return 0

#用户列表-删除
@app.route("/UserDelete",methods=["post","get"])
def UserDelete():
    id = request.args.get("id")
    conn=MySQLHelper();
    sql1="select uname,upwd from tb_user where id=%s"
    rs=conn.executeForSelectAll(sql1,(id))
    sql2="delete from tb_address where uname=%s and upwd=%s"
    conn.executeForUCD(sql2,(rs[0][0],rs[0][1]))
    sql3="delete from tb_user where id=%s "
    conn.executeForDelete(sql3,(id))

    return render_template("userList.html")

#用户列表-修改权限
@app.route("/PowerUpdate",methods=["post","get"])
def PowerUpdate():
    id = request.args.get("id")
    conn=MySQLHelper();
    sql="select static from tb_user where id=%s "
    s=conn.executeForSelectAll(sql,(id))
    print(s)
    if s[0][0]==1:
        sql0="update tb_user set static=0 where id=%s"
        conn.executeForUpdate(sql0,(id))
    else:
        sql1="update tb_user set static=1 where id=%s"
        conn.executeForUpdate(sql1,(id))
    return render_template("userList.html")

#注册
@app.route("/register",methods=["post"])
def register():
    conn=MySQLHelper();
    sql="insert into tb_user values(null,%s,md5(%s),%s,%s,%s,%s)"
    i=conn.executeForUCD(sql,[request.form.get("uname"),request.form.get("upwd"),request.form.get("sex"),request.form.get("email"),request.form.get("phone"),0])
    if i>0 :
        return "1"
    else:
        return "0"

#登录
@app.route("/login",methods=["post"])
def login():
    uname=request.form.get("l_uname","")
    upwd=request.form.get("l_upwd","")
    #print(request.form.__str__())
    conn=MySQLHelper();
    sql="select * from tb_user where uname=%s and upwd=md5(%s)"
    rs=conn.executeForSelectAll(sql,(uname,upwd))
    i=rs.__len__()
    #获取用户权限
    sql2="select static,id from tb_user where uname=%s and upwd=md5(%s)"
    q=conn.executeForSelectAll(sql2,(uname,upwd))
    global power
    global uid
    power=q[0][0]
    uid=q[0][1]
    if i==0:
        return "0"#同步请求 fault
    else:
        return "1"  # success

#个人中心-数据更新
@app.route("/UpdateUser",methods=["post","get"])
def UpdateUser():
    global uid
    conn=MySQLHelper();
    sql="update tb_user set uname=%s,upwd=md5(%s),sex=%s,email=%s,phone=%s where id=%s"
    conn.executeForUpdate(sql,[request.form.get("uname"),request.form.get("upwd"),request.form.get("sex"),request.form.get("email"),request.form.get("phone"),uid])
    return "1"

#个人中心-地址
@app.route("/Useraddress",methods=["post","get"])
def Useraddress():
    global uid
    conn=MySQLHelper();
    sql="select uname,upwd from tb_user where id=%s"
    q=conn.executeForSelectAll(sql,(uid))
    name=q[0][0]
    pwd=q[0][1]
    sqls="select * from tb_address where uname=%s and upwd=%s"
    s=conn.executeForSelectAll(sqls,(name,pwd))
    print(s)
    address=request.form.get("uaddress")
    print(address)
    if s.__len__()==0:
        sqlad="insert into tb_address values(null,%s,%s,null)"
        conn.executeForUCD(sqlad,(name,pwd))
        sqladd="update tb_address set address=%s where uname=%s and upwd=%s"
        conn.executeForUpdate(sqladd,(address,name,pwd))
    else:
        sqladd="update tb_address set address=%s where uname=%s and upwd=%s"
        conn.executeForUpdate(sqladd,(address,name,pwd))
    return "1"

#获取个人信息
@app.route("/getPersonal")
def getPersonal():
    global uid
    conn=MySQLHelper();
    sql1="select * from tb_user where id=%s"
    g=conn.executeForSelectAll(sql1,(uid))
    uname=g[0][1]
    upwd=g[0][2]
    sql2="select address from tb_address where uname=%s and upwd=%s"
    a=conn.executeForSelectAll(sql2,(uname,upwd))
    rs=g+a
    return json.dumps(rs)

#爬虫
@app.route("/spider")
def spider():
    url="http://www.xbiquge.la/"
    header={"User-Agent":"Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/89.0.4389.72 Safari/537.36 Edg/89.0.774.45"}
    r=requests.get(url=url,headers=header)
    ht=etree.HTML(r.text)
    clas1=ht.xpath("//div[@class='content']/h2/text()")
    clas1_1=ht.xpath("//div[@class='content border']/h2/text()")
    title1=ht.xpath("//div[@class='top']//dl/dt/a/text()")
    title2=ht.xpath("//div[@class='content']//li/a/text()")
    title3=ht.xpath("//div[@class='content border']//li/a/text()")
    author1=ht.xpath("//div[@class='content']//li/text()")
    author2=ht.xpath("//div[@class='content border']//li/text()")
    conn=MySQLHelper()
    j=0
    for i in range(6):
        sql="insert into tb_book values(%s,null,%s,'999')"
        if(i==2):
            conn.executeForUCD(sql,(title1[i],clas1_1[0]))
        elif(i==5):
            conn.executeForUCD(sql,(title1[i],clas1_1[1]))
        else:
            conn.executeForUCD(sql,(title1[i],clas1[j]))
            j+=1
    j=0
    for i in range(len(title2)):
        sql="insert into tb_book values(%s,%s,%s,'999')"
        if(i==12):
            j+=1
        if(i==24):
            j+=1
        if(i==36):
            j+=1
        conn.executeForUCD(sql,(title2[i],author1[i],clas1[j]))
    j=0
    for i in range(len(title3)):
        sql="insert into tb_book values(%s,%s,%s,'999')"
        if(i==12):
            j+=1
        conn.executeForUCD(sql,(title3[i],author2[i],clas1_1[j]))
    return "success"

#书籍查询
@app.route("/xuanBook")
def xuanBook():
    conn=MySQLHelper()
    sql="select * from tb_book where class='玄幻小说'"
    rs=conn.executeForSelectAll(sql,())
    return json.dumps(rs)
@app.route("/xiuBook")
def xiuBook():
    conn=MySQLHelper()
    sql="select * from tb_book where class='修真小说'"
    rs=conn.executeForSelectAll(sql,())
    return json.dumps(rs)
@app.route("/duBook")
def duBook():
    conn=MySQLHelper()
    sql="select * from tb_book where class='都市小说'"
    rs=conn.executeForSelectAll(sql,())
    return json.dumps(rs)
@app.route("/chuanBook")
def chuanBook():
    conn=MySQLHelper()
    sql="select * from tb_book where class='穿越小说'"
    rs=conn.executeForSelectAll(sql,())
    return json.dumps(rs)
@app.route("/wangBook")
def wangBook():
    conn=MySQLHelper()
    sql="select * from tb_book where class='网游小说'"
    rs=conn.executeForSelectAll(sql,())
    return json.dumps(rs)
@app.route("/keBook")
def keBook():
    conn=MySQLHelper()
    sql="select * from tb_book where class='科幻小说'"
    rs=conn.executeForSelectAll(sql,())
    return json.dumps(rs)
@app.route("/allBook")
def allBook():
    conn=MySQLHelper()
    sql="select * from tb_book"
    rs=conn.executeForSelectAll(sql,())
    return json.dumps(rs)
@app.route("/selectBook",methods=["post"])
def selectBook():
    conn=MySQLHelper()
    sc=request.form.get("sc")
    title=request.form.get("title")
    print(sc)
    if(sc=="bname"):
        sql="select * from tb_book where bname=%s"
        rs=conn.executeForSelectAll(sql,(title))
        if rs.__len__()==0:
            return "0"
        else:
            return json.dumps(rs)
    else:
        sql="select * from tb_book where author=%s"
        rs=conn.executeForSelectAll(sql,(title))
        if rs.__len__()==0:
            return "0"
        else:
            return json.dumps(rs)

#书籍信息
@app.route("/bookInfo")
def bookInfo():
    global bbname
    conn=MySQLHelper()
    sql="select * from tb_book where bname=%s"
    rs=conn.executeForSelectAll(sql,(bbname))
    return json.dumps(rs)
@app.route("/novel")
def novel():
    global bbname
    bbname=request.args.get("name")
    conn=MySQLHelper()
    sql1="select class from tb_book where bname=%s"
    rs=conn.executeForSelectAll(sql1,(bbname))
    sql2="update tb_class_click set click_num=click_num+1 where class=%s"
    conn.executeForUCD(sql2,(rs[0][0]))
    return render_template("novel.html")

#图书管理
@app.route("/getBookInfo",methods=["get"])
def getBookInfo():
    global power
    if(power==1):
        conn=MySQLHelper();
        sql="select * from tb_book"
        rs=conn.executeForSelectAll(sql,())
        return json.dumps(rs)
    else:
        return 0
#图书管理-添加
@app.route("/UpdateBook",methods=["post"])
def UpdateBook():
    print("111")
    conn=MySQLHelper();
    sql="insert into tb_book values(%s,%s,%s,%s,%s)"
    i=conn.executeForUCD(sql,[request.form.get("u_bname"),request.form.get("u_author"),request.form.get("u_class"),request.form.get("u_number"),request.form.get("u_rich")])
    if i>0 :
        return "1"
    else:
        return "0"
#图书管理-删除
@app.route("/DelBook",methods=["get"])
def DelBook():
    bname = request.args.get("bname")
    conn=MySQLHelper();
    sql="delete from tb_book where bname=%s "
    conn.executeForDelete(sql,(bname))
    return render_template("Bookmanage.html")
#图书管理-修改数量
@app.route("/UpBooknumber",methods=["post"])
def UpBooknumber():
    conn=MySQLHelper();
    number=request.form.get("up_number")
    bname=request.form.get("s_bname")
    #number=int(number)
    sql="update tb_book set number=%s where bname=%s"
    i=conn.executeForUCD(sql,(number,bname))
    if i==1:
        return "1"
    else:
        return "0"
#图书管理-修改价钱
@app.route("/UpBookrich",methods=["post"])
def UpBookrich():
    conn=MySQLHelper();
    rich=request.form.get("up_rich")
    bname=request.form.get("s_bname")
    #number=int(number)
    sql="update tb_book set rich=%s where bname=%s"
    i=conn.executeForUCD(sql,(rich,bname))
    if i==1:
        return "1"
    else:
        return "0"
#愿望单
@app.route("/wish")
def wish():
    try:
        bname=request.args.get("bname")
        conn=MySQLHelper()
        sql1="select author,class,rich from tb_book where bname=%s"
        rs=conn.executeForSelectAll(sql1,(bname))
        sql2="insert into tb_wish values(%s,%s,%s,%s)"
        conn.executeForUCD(sql2,(bname,rs[0][0],rs[0][1],rs[0][2]))
        return render_template("Wishlist.html")
    except:
        return render_template("Wishlist.html")
#愿望单列表
@app.route("/wishinfo")
def wishinfo():
    conn=MySQLHelper()
    sql="select * from tb_wish "
    rs=conn.executeForSelectAll(sql,())
    return json.dumps(rs)
#清空愿望单
@app.route("/delwish")
def delwish():
    conn=MySQLHelper()
    sql="truncate table tb_wish"
    conn.executeForUCD(sql,())
    return render_template("Wishlist.html")
#愿望单删除
@app.route("/DelWish",methods=["get"])
def DelWish():
    bname = request.args.get("bname")
    conn=MySQLHelper();
    sql="delete from tb_wish where bname=%s "
    conn.executeForDelete(sql,(bname))
    return render_template("Wishlist.html")


#购物车
@app.route("/shopping")
def shopping():
    try:
        bname=request.args.get("bname")
        conn=MySQLHelper()
        sql1="select author,class,rich from tb_book where bname=%s"
        rs=conn.executeForSelectAll(sql1,(bname))
        sql2="insert into tb_shopping values(%s,%s,%s,%s)"
        conn.executeForUCD(sql2,(bname,rs[0][0],rs[0][1],rs[0][2]))
        return render_template("ShoppingCart.html")
    except:
        return render_template("ShoppingCart.html")
#购物车信息
@app.route("/shoppinginfo")
def shoppinginfo():
    conn=MySQLHelper()
    sql="select * from tb_shopping "
    rs=conn.executeForSelectAll(sql,())
    return json.dumps(rs)
#清空购物车
@app.route("/delshop")
def delshop():
    conn=MySQLHelper()
    sql="truncate table tb_shopping"
    conn.executeForUCD(sql,())
    return render_template("ShoppingCart.html")
#购买
@app.route("/buybook",methods=["post"])
def buybook():
    bname=request.form.get("bname")
    number=request.form.get("number")
    conn=MySQLHelper()
    sql1="select * from tb_book where bname=%s"
    rs=conn.executeForSelectAll(sql1,(bname))
    surplus=rs[0][3]
    if int(number)>=rs[0][3]:
        return rs[0][0]+"数量不足，当前数量："+str(rs[0][3])
    else:
        n=surplus-int(number)
        n=str(n)
        sql2="update tb_book set number=%s where bname=%s"
        conn.executeForUCD(sql2,(n,bname))
        sql3="delete from tb_shopping where bname=%s"
        conn.executeForDelete(sql3,(bname))
        return "1"
#购物车删除
@app.route("/Delbuy",methods=["get"])
def Delbuy():
    bname = request.args.get("bname")
    conn=MySQLHelper();
    sql="delete from tb_shopping where bname=%s "
    conn.executeForDelete(sql,(bname))
    return render_template("ShoppingCart.html")

#退出登录
@app.route("/exit")
def exit():
    global power
    power=0
    conn=MySQLHelper()
    sql1="truncate table tb_shopping"
    conn.executeForUCD(sql1,())
    sql2="truncate table tb_wish"
    conn.executeForUCD(sql2,())
    return render_template("index.html")

#echarts图
@app.route("/getUserDataForBar")
def getUserDataForBar():
    conn=MySQLHelper()
    sql="SELECT class,COUNT(*) AS `count` from tb_book GROUP BY class"
    rs=conn.executeForSelectAll(sql,())
    # 定义柱状图的两个列表
    list1 = []
    list2 = []
    # 构造字典
    dict1 = {}
    for item in rs:  # 遍历rs
        list1.append(item[0])
        list2.append(item[1])
    dict1["enable"] = list1;
    dict1["count"] = list2;
    return json.dumps(dict1,ensure_ascii=False)

@app.route("/getUnivTagsDataForPie")
def getUnivTagsDataForPie():
    conn = MySQLHelper()
    #sql="select class,count(*) as `count` from tb_book GROUP BY class"
    sql="select * from tb_class_click "
    rs=conn.executeForSelectAll(sql,())
    list1=[]
    for item in rs:
        dict1={}
        dict1["name"]=item[0]
        dict1["value"]=item[1]
        list1.append(dict1)
    print(list1)
    return json.dumps(list1,ensure_ascii=False)

#机器人
@app.route("/robot")
def robot():
    return  render_template("robot.html")


@app.route("/manager")
def manager():
    return render_template("admin.html")

@app.route('/',methods=["get","post"])
def index():
    return render_template("index.html")

@app.route("/welcome")
def welcome():
    return  render_template("welcome.html")

@app.route("/bookList")
def articleList():
    return  render_template("bookList.html")

@app.route("/analysis")
def analysis():
    return  render_template("analysis.html")

@app.route("/userList")
def userList():
    return  render_template("userList.html")


@app.route("/ShoppingCart")
def ShoppingCart():
    return  render_template("ShoppingCart.html")

@app.route("/Wishlist")
def Wishlist():
    return  render_template("Wishlist.html")

@app.route("/user")
def user():
    return  render_template("user.html")

@app.route("/Bookmanage")
def Bookmanage():
    return  render_template("Bookmanage.html")

@app.route("/backlogin")
def backlogin():
    return  render_template("index.html")

@app.route("/regi")
def regi():
    return  render_template("regi.html")

@app.route("/say")
def say():
    saycontent=robot1()
    # print(saycontent)
    return saycontent

@app.route("/send",methods=["post"])
def send():
    txt=request.form.get("txt")
    print(txt)
    saycontent=robot2(txt)
    print(saycontent)
    return json.dumps(saycontent)
@app.route("/speak",methods=["get"])
def speak():
    saycontent=audioConvertToTxt(rate=16000)
    print(saycontent)
    return json.dumps(saycontent)



if __name__ == '__main__':
    app.run(host="localhost",port=80,debug=True)
